package com.sq.partner.manager.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import com.sq.partner.manager.dao.ITradPlanGroupDao;
import com.sq.partner.manager.mybatis.model.TradInsuPlan;
import com.sq.partner.manager.mybatis.model.TradPlanGroup;
import com.sq.partner.manager.util.DBUtil;
import com.sq.partner.manager.util.LogUtil;
import com.sq.partner.manager.util.Page;
import com.sq.partner.manager.util.SqlUtil;
import com.sq.partner.manager.util.StrUtil;
@Repository
public class TradPlanGroupDaoImpl extends BaseDaoImpl implements ITradPlanGroupDao {
	
	private static Logger logger = LogUtil.getLog(TradPlanGroupDaoImpl.class);

	@Override
	public TradPlanGroup queryTradPlanGroupID(TradPlanGroup group) throws Exception {
		Object obj = this.query("queryTradPlanGroupID", group);
		if(null!=obj){
			return (TradPlanGroup)obj;
		}
		return null;
	}

	@Override
	public Page<TradPlanGroup> queryPage(Page<TradPlanGroup> page)
			throws Exception {
		Connection conn = null;
		//查总条数
		PreparedStatement pstmt = null;
		//查分页
		PreparedStatement pstmt1 = null;
		ResultSet rs = null;
		try{
			conn = this.getConnection();
			StringBuffer sqlbuf=new StringBuffer();
			sqlbuf.append("select g.group_id as groupId,g.group_name as groupName,g.group_desc as groupDesc,dic.code_name as facadeAppTypeName,g.partner_code as partnerCode,decode(g.group_status,1,'有效','无效') as groupStatus,g.facade_app_type as facadeAppType from t_trad_plan_group g,t_gateway_dict dic where 1=1 and g.facade_app_type = dic.code and dic.code_type = 'facade_app_type' and g.is_prod_group <> 1");
			String sql;
			List<String> list=new ArrayList<String>();
			List<Boolean> islike=new ArrayList<Boolean>();
			TradPlanGroup tradInsuGroup= page.getType();

			if(StrUtil.isNotEmpty(tradInsuGroup.getGroupName())){
				sqlbuf.append(" and g.group_name like ?");
				list.add(tradInsuGroup.getGroupName());
				islike.add(true);
			}
			
			sqlbuf.append(" order by g.oper_time desc");
			sql=sqlbuf.toString();
			logger.debug("执行SQL：" + sql);


			pstmt = conn.prepareStatement("select count(*) from ("+sql+")");
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt.setObject(i+1, list.get(i));
				}
				
			}
			rs = pstmt.executeQuery();
			rs.next();
			Long total = rs.getLong(1);
			page.setTotal(total);
			//查出分页
			SqlUtil sqlUtil=new	SqlUtil();
			String pagesql=sqlUtil.buildPageSqlForOracle(sql,page).toString();
			logger.debug("执行SQL："+pagesql);
			pstmt1 = conn.prepareStatement(pagesql);
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt1.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt1.setObject(i+1, list.get(i));
				}
			}
			rs = pstmt1.executeQuery();
			List lists = DBUtil.transRs2List(rs);
			page.setData(lists);
		}finally{
			close(rs);
			close(pstmt);
			close(pstmt1);
			close(conn);
		}
		
		return page;
	}

	@Override
	public boolean addTradPlanGroup(TradPlanGroup group) throws Exception {
		return this.add("insertTradPlanGroup", group);
	}

	@Override
	public boolean editTradPlanGroup(TradPlanGroup group) throws Exception {
		return this.update("updateTradPlanGroupNotNull", group);
	}

	@Override
	public boolean deleteTradPlanGroup(Long id) throws Exception {
		return this.delete("delTradPlanGroupById", id);
	}

	@Override
	public Page<TradInsuPlan> queryTradInsuPlanPage(Page<TradInsuPlan> page,Long groupId,String partnerCode) throws Exception {
		Connection conn = null;
		//查总条数
		PreparedStatement pstmt = null;
		//查分页
		PreparedStatement pstmt1 = null;
		ResultSet rs = null;
		try{
			conn = this.getConnection();
			StringBuffer sqlbuf=new StringBuffer();
			sqlbuf.append("select insu.insu_plan_no as insuPlanNo,insu.insu_plan_name as insuPlanName,pg.required as required,decode(pg.insu_plan_no,null,1,0) as isChecked ");

			String sql;
			List<String> list=new ArrayList<String>();
			List<Boolean> islike=new ArrayList<Boolean>();
			TradInsuPlan tradInsuPlan= page.getType();

			//方案组ID
			if(null!=groupId){
				sqlbuf.append(" from (select gg.insu_plan_no,gg.required from t_trad_insu_plan_group gg where gg.plan_group_id = ? ) pg,t_trad_insu_plan insu where 1=1 and  insu.insu_plan_no = pg.insu_plan_no(+)");
				list.add(groupId.toString());
				islike.add(false);
			}
			//合作伙伴代码
			if(StrUtil.isNotEmpty(partnerCode)){
				sqlbuf.append(" and insu.partner_no = ? ");
				list.add(partnerCode);
				islike.add(false);
			}
			
	/*
			if(null!=groupId){
				sqlbuf.append(" and tradInsuPlan.partner_no = (select pg.partner_code from t_trad_plan_group pg where pg.group_id = ? ) ");
				list.add(groupId.toString());
				islike.add(false);
			}
		*/	
			if(StrUtil.isNotEmpty(tradInsuPlan.getInsuPlanNo())){
				sqlbuf.append(" AND insu.INSU_PLAN_NO like ?");
				list.add(tradInsuPlan.getInsuPlanNo());
				islike.add(true);
			}
			if(StrUtil.isNotEmpty(tradInsuPlan.getInsuPlanName())){
				sqlbuf.append(" AND insu.INSU_PLAN_NAME like ?");
				list.add(tradInsuPlan.getInsuPlanName());
				islike.add(true);
			}
			
			
			sqlbuf.append(" order by pg.required");
			sql=sqlbuf.toString();
			logger.debug("执行SQL：" + sql);


			pstmt = conn.prepareStatement("select count(*) from ("+sql+")");
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt.setObject(i+1, list.get(i));
				}
				
			}
			rs = pstmt.executeQuery();
			rs.next();
			Long total = rs.getLong(1);
			page.setTotal(total);
			//查出分页
			SqlUtil sqlUtil=new	SqlUtil();
			String pagesql=sqlUtil.buildPageSqlForOracle(sql,page).toString();
			logger.debug("执行SQL："+pagesql);
			pstmt1 = conn.prepareStatement(pagesql);
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt1.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt1.setObject(i+1, list.get(i));
				}
			}
			rs = pstmt1.executeQuery();
			List lists = DBUtil.transRs2List(rs);
			page.setData(lists);
		}finally{
			close(rs);
			close(pstmt);
			close(pstmt1);
			close(conn);
		}
		return page;
	}

	@Override
	public List<TradPlanGroup> queryGroupByMap(Map<String, Object> map)  throws Exception {
		
		return  this.queryList("queryTradPlanGroupByMap", map);
	}

	@Override
	public Page<Map<String,Object>> queryGroupByPage(Page<Map<String,Object>> page)
			throws Exception {
		Connection conn = null;
		//查总条数
		PreparedStatement pstmt = null;
		//查分页
		PreparedStatement pstmt1 = null;
		ResultSet rs = null;
		try{
			conn = this.getConnection();
			StringBuffer sqlbuf=new StringBuffer();
			sqlbuf.append("select g.Group_Id,g.group_name,g.group_desc,g.partner_code,g.sms_template_code,g.Seefee_Special_Flag,g.GROUP_EURL,g.IS_CORE_MESSAGE,g.MESSAGE_RECEIVER,case when   length( g.mail_template) >40  then substr( g.mail_template , 0,40)||'......'   ELSE  g.mail_template  end  mail_show,g.mail_template mail_template,g.group_code,info.channel_name from t_trad_plan_group g left join t_gateway_channel_info info on g.partner_code=info.channel_code   left join T_TRAD_SMSTEMPLATE  tem  on tem.sms_template_code=g.sms_template_code   where g.is_prod_group ='0' and g.group_status='1' ");

			String sql;
			List<String> list=new ArrayList<String>();
			List<Boolean> islike=new ArrayList<Boolean>();
			Map<String,Object> map= page.getMap();
			if(null!=map.get("groupCode")){
				String groupCode=(String) map.get("groupCode");
				sqlbuf.append(" AND g.group_code like ?");
				list.add("%"+groupCode+"%");
				islike.add(true);
			}
			if(map.get("groupName")!=null){
				String groupName=(String) map.get("groupName");
				sqlbuf.append(" AND g.group_name like ?");
				list.add("%"+groupName+"%");
				islike.add(true);
			}
			
			
			sqlbuf.append(" order by  g.oper_time desc");
			sql=sqlbuf.toString();
			logger.debug("执行SQL：" + sql);
			pstmt = conn.prepareStatement("select count(*) from ("+sql+")");
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt.setObject(i+1, list.get(i));
				}
				
			}
			rs = pstmt.executeQuery();
			rs.next();
			Long total = rs.getLong(1);
			page.setTotal(total);
			//查出分页
			SqlUtil sqlUtil=new	SqlUtil();
			String pagesql=sqlUtil.buildPageSqlForOracle(sql,page).toString();
			logger.debug("执行SQL："+pagesql);
			pstmt1 = conn.prepareStatement(pagesql);
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt1.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt1.setObject(i+1, list.get(i));
				}
			}
			rs = pstmt1.executeQuery();
			List lists = DBUtil.transRs2List(rs);
			page.setData(lists);
		}finally{
			close(rs);
			close(pstmt);
			close(pstmt1);
			close(conn);
		}
		return page;
	}

	@Override
	public Page<Map<String, Object>> queryGroupPlanByPage(
			Page<Map<String, Object>> page) throws Exception {
		Connection conn = null;
		//查总条数
		PreparedStatement pstmt = null;
		//查分页
		PreparedStatement pstmt1 = null;
		ResultSet rs = null;
		try{
			conn = this.getConnection();
			StringBuffer sqlbuf=new StringBuffer();
			sqlbuf.append(" select * from (select plan.plan_no planCode , plan.plan_name PlanName,plan.is_see_money seeMoney, '0' planType,plan.partner_no partnerCode from T_NLIFE_PLAN plan where plan.state='1' union select  nocar.plan_code planCode , nocar.plan_name PlanName, nocar.is_see_money seeMoney, '1' plantype ,  nocar.PARTNER_CODE partnerCode");
			sqlbuf.append(" from t_nocar_plan nocar where nocar.status='1' ) p left join  (select  *  from t_trad_insu_plan_group where 1=1 ");
			String sql;
			List<String> list=new ArrayList<String>();
			List<Boolean> islike=new ArrayList<Boolean>();
			Map<String,Object> map= page.getMap();
			if(null!=map.get("id")){
				String id=(String) map.get("id");
				sqlbuf.append(" and plan_group_id = ? ");
				list.add(id);
				islike.add(false);
			}
			sqlbuf.append(" ) t  on t.insu_plan_no=p.plancode  where  1=1 ");
			if(null!=map.get("partnerCode")){
				String partnerCode=(String) map.get("partnerCode");
				sqlbuf.append(" AND partnerCode = ?");
				list.add(partnerCode);
				islike.add(false);
			}
			if(map.get("isSeeMoney")!=null){
				String isSeeMoney=(String) map.get("isSeeMoney");
				sqlbuf.append(" AND  p.seemoney = ?");
				list.add(isSeeMoney);
				islike.add(false);
			}
			if(null!=map.get("planCode")){
				String planCode=(String) map.get("planCode");
				sqlbuf.append(" AND planCode like ?");
				list.add("%"+planCode+"%");
				islike.add(true);
			}
			if(null!=map.get("planName")){
				String planName=(String) map.get("planName");
				sqlbuf.append(" AND planName like ?");
				list.add("%"+planName+"%");
				islike.add(true);
			}
			sqlbuf.append(" order by plan_group_id,planCode ");
			sql=sqlbuf.toString();
			logger.debug("执行SQL：" + sql);
			pstmt = conn.prepareStatement("select count(*) from ("+sql+")");
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt.setObject(i+1, list.get(i));
				}
				
			}
			rs = pstmt.executeQuery();
			rs.next();
			Long total = rs.getLong(1);
			page.setTotal(total);
			//查出分页
			SqlUtil sqlUtil=new	SqlUtil();
			String pagesql=sqlUtil.buildPageSqlForOracle(sql,page).toString();
			logger.debug("执行SQL："+pagesql);
			pstmt1 = conn.prepareStatement(pagesql);
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt1.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt1.setObject(i+1, list.get(i));
				}
			}
			rs = pstmt1.executeQuery();
			List lists = DBUtil.transRs2List(rs);
			page.setData(lists);
		}finally{
			close(rs);
			close(pstmt);
			close(pstmt1);
			close(conn);
		}
		return page;
	}
	
	@Override
	public List<Map<String, Object>> impGroupPlan(Map<String, Object> map)
			throws Exception {
		return this.queryList("impGroupPlanByIdMap", map);
	}
}
